<?php
namespace app\company\controller;

use app\common\lib\SendIntegralNew;
use think\Db;
use think\Exception;
use think\Request;
use League\Csv\Reader;
use League\Csv\Writer;
use League\Csv\CharsetConverter;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Organization extends Base {

    private $where = [];

    public function _initialize(){
        parent::_initialize();
        $this->where = ['com_id'=>$this->com_id];
        $this->assign('user_status', [1=>'否', 2=>'是']);
    }
    /**
     * 部门列表
     */
    public function index(){
        $list = db('com_department')->where($this->where)->select();
        $list = toTree($list);
        $list = organization_tree($list);
        $this->assign('list', $list);

        $this->base_department_page();//部门列表

        return $this->fetch();
    }

    public function organization_edit(){
        if(request()->isPost()){
            return $this->organization_store();
        }
        $com_department_list = db('com_department')->where($this->where)->field('id, pid, name')->select();
        $com_department_list = toTree($com_department_list);
        $com_department_list = organization_tree($com_department_list);

        if(input('id')){
            $where['a.id'] = input('id');
            $info = M('com_department a')
                ->join('users b','b.id=a.first_check_user_id','left')
                ->join('users c','c.id=a.last_check_user_id','left')
                ->join('com_department d','d.id=a.pid','left')
                ->where($where)
                ->field('a.*,b.name first_name,c.name last_name,d.name p_name')
                ->find();
            $this->assign('info', $info);
        }
        $this->assign('com_department_list', $com_department_list);
        return $this->fetch();
    }

    public function organization_del(){
        $params = input();
        $ids = explode(',', $params['id']);
        $this->where['id'] = ['in', $ids];
        $com_department_ids = db('com_department')->where($this->where)->column('id');

        $com_department_exists = db('com_department')->where(['pid'=>['in', $com_department_ids]])->find();
        $users_exists = db('users')->where(['department_id'=>['in', $com_department_ids]])->find();
        if($com_department_exists || $users_exists){
            return $this->ajaxReturn(1, '删除失败 需先删除下级部门或人员列表');
        }

        db('com_department')->where('id', 'in', $com_department_ids)->delete();
        return $this->ajaxReturn(0, '成功');
    }

    public function organization_store(){
        $params = input();

        $result = $this->validate(
            $params,
            ['name|部门名称'  => 'require|max:50']
        );
        unset($params['first_check_user_name']);
        unset($params['last_check_user_name']);
        unset($params['pid_name']);
        if(true !== $result){
            return $this->ajaxReturn(1, $result);
        }
        if($params['pid'] == $params['id']){
            return $this->ajaxReturn(1, '上级部门不能是本部门');
        }
        $where_department=[];
        $where_department['name'] = $params['name'];
        $where_department['com_id'] = $this->com_id;
        if($params['id']){
            $where_department['id'] = ['neq',$params['id']];
            $list = M('com_department')->where($where_department)->find();
            if(!empty($list)){
                return $this->ajaxReturn(1,'该名称已存在');
            }
            $com_department_info = M('com_department a')
                ->join('users b','b.id=a.first_check_user_id','left')
                ->join('users c','c.id=a.last_check_user_id','left')
                ->where('a.id',$params['id'])
                ->field('a.*,b.name first_name,c.name last_name')
                ->find();
            //如果上级分类选的自己则删除该上级参数
            if($params['pid'] == $params['id']){
                unset($params['pid']);
            }elseif($com_department_info['pid'] != $params['pid']){
                //如果修改了原本的上级id则检查其他部门上级id
                $exists = db('com_department')->where(['pid'=>$params['id']])->find();
                if($exists){
                    return $this->ajaxReturn(1, '修改失败 需先修改或删除下级部门');
                }
            }
            db('com_department')->where('id',$params['id'])->update($params);
        }else{
            $list = M('com_department')->where($where_department)->find();
            if(!empty($list)){
                return $this->ajaxReturn(1,'该名称已存在');
            }
            $params['com_id'] = $this->com_id;
            $params['create_time'] = time();
            db('com_department')->insert($params);
        }
        return $this->ajaxReturn(0,"成功", url(CONTROLLER_NAME.'/index'));
    }

    /**
     * 人员列表
     */
    public function staff(){
        $this->set_tem_val();
        $where = $this->where;
        input('type') && $where['type'] = input('type');
        input('department_id') && $where['department_id'] = input('department_id');
        //递归部门id
        if($where['department_id']??''){
            $department_childs = department_child([$where['department_id']], $this->com_id);
            $where['department_id'] = ['in', $department_childs];
        }

        input('search') && $where['name|job_number|account'] = ['like', trim(input('search')).'%'];
        if(input('export') == 1){
            $this->staff_explode($where);
        }
        $type = I('type',1);
        $where['type'] = $type;
        $where['role_id'] = ['neq',0];
        $list = db('users')->where('com_id', $this->com_id)->where($where)->order('id desc')
//            ->select();
            ->paginate('',false,page_param());
//        var_dump($list);die;
        $arr_list = $list->items();
        $conf = db('com_config')->where('com_id', $this->com_id)->find();
        if($conf){
            $now_time = time();
            $end_time = 0; //用于计算工龄分的结束时间
            //如果设置了工龄分截止时间就按该时间计算  则否按当前时间计算
            if($conf['working_age_end']){
                if($conf['working_age_end'] < $now_time){
                    $end_time = $conf['working_age_end'];
                }else{
                    $end_time = $now_time;
                }
            }else{
                $end_time = $now_time;
            }
            if($conf['working_age_type'] == 0){
                $interval = \DateInterval::createFromDateString('1 month');
            }elseif($conf['working_age_type'] == 1){
                $interval = \DateInterval::createFromDateString('1 day');
            }
            $dataTimeEnd = new \DateTime(date('Y-m-d', $end_time));
            $date = new \DateTime();
            foreach($arr_list as $k=>$v){
                $dataTimeStart = new \DateTime(date('Y-m-d', $v['entry_time']));
                $period   = new \DatePeriod($dataTimeStart, $interval, $dataTimeEnd);
                $period_num = 0;
                foreach($period as $v2){
                    $period_num++;
                }
                $arr_list[$k]['start_integral'] = $conf['start_integral'];
                if(!empty($v['birthday'])){
                    if($v['birthday'] <0) {
                        $date->setTimestamp((int)$v['birthday']);

                        $arr_list[$k]['birthday'] = $date->format('Y-m-d');
                    }else{
                        $arr_list[$k]['birthday'] = date('Y-m-d',$v['birthday']);
                    }
                }
//                $arr_list[$k]['working_age_integral'] = $conf['working_age_integral'] * $period_num;
            }
        }else{
            $this->error("请先设置积分配置");
        }
        $this->assign('list', $list);
        $this->assign('arr_list', $arr_list);

//        if($type == 1){
            return $this->fetch();
//        }else{
//            return $this->fetch('staff_freeze');
//        }

    }

    public function staff_add(){
        if(request()->isPost()){
            return $this->staff_store();
        }
        $this->set_tem_val();

        $this->base_department_page();

        return $this->fetch('staff_edit');
    }

    public function staff_edit(){
        if(request()->isPost()){
            return $this->staff_store();
        }
        $this->set_tem_val();
        if(input('id')){
            $info = db('users')->alias('u')
                ->join('com_prize_buckle_relation_users cpb', 'cpb.user_id=u.id', 'LEFT')
                ->join('com_department d', 'd.id=u.department_id', 'LEFT')
                ->where(['u.id'=>input('id'), 'u.com_id'=>$this->com_id])
                ->field('u.*, cpb.prize_buckle_id, d.name department_name')->find();
//            var_dump($info);die;

            $datetime = new \DateTime();
            if(!empty($info['birthday'])){
                $datetime->setTimestamp($info['birthday']);
                $info['birthday'] = $datetime->format('Y-m-d');
            }
            $info['entry_time'] = date("Y-m-d", $info['entry_time']);
            $this->assign('info', $info);
        }
        $this->base_department_page();
        return $this->fetch();
    }
    //设置人员列表公共数据
    public function set_tem_val(){
        $this->base_department();//弹窗部门需要的数据
        $list = db('com_department')->where($this->where)->select();
        $list = toTree($list);
        $list = organization_tree($list);
        $where = [];
        $where['com_id'] = ['in',[0,$this->com_id]];
        $role_list = db('com_roles')->where($where)->field('id, name')->select();
        $com_prize_buckle_list = db('com_prize_buckle')->where($this->where)->field('id, name')->select();
        $data = [
            'com_department'=>$list,  //部门列表
            'role_list'=>$role_list, //角色列表
            'com_prize_buckle_list'=>$com_prize_buckle_list, //奖扣权限
        ];
        $this->assign($data);
    }
    //启用/冻结
    public function staff_del(){
        $params = input();
        $ids = explode(',', $params['id']);
        if(in_array($params['status'], [1, 2])){
            if($params['status'] == 1){
                //判断企业限制人数.
                $people_max = M('company')->where('id',$this->com_id)->value('use_people');
                //判断已有人数
                $where_people = [
                    'com_id'=>$this->com_id,
                    'type'=>1
                ];
                $people_num = M('users')->where($where_people)->count();
                $num = $people_max - $people_num + 1;
                if(count($ids)> $num){
                    return $this->ajaxReturn(1, '超过企业限制人数无法启用');
                }
            }
            db('users')->where(['id'=>['in', $ids]])->update(['type'=>$params['status'],'role_id'=>1]);
            //清除组用户数据
            M('com_statistics_group_relation_users')->where(['user_id'=>['in', $ids]])->delete();
            M('com_prize_buckle_relation_users')->where(['user_id'=>['in', $ids]])->delete();
            //清楚固定积分人员
            M('com_fixed_relation_users')->where(['user_id'=>['in', $ids]])->delete();
            return $this->ajaxReturn(0, '成功');
        }
    }

    public function staff_store(){

        $params = input();
        $params['com_id'] = $this->com_id;

        $result = $this->validate($params,'Comuser');
        if(true !== $result){
            return $this->ajaxReturn(1, $result);
        }
        $params['birthday'] = strtotime($params['birthday']);
        $params['entry_time'] = strtotime($params['entry_time']);
        //检查企业的 角色权限 奖扣权限 部门 参数
        $role_id = db('com_roles')->where(['id'=>$params['role_id'], 'com_id'=>['in',[$this->com_id,0]]])->value('id');
        if(!$role_id){
            return $this->ajaxReturn(1, '角色信息有误');
        }
        $com_prize_buckle_id = db('com_prize_buckle')->where(['id'=>$params['cpb_id'], 'com_id'=>$this->com_id])->value('id');
        if(!$com_prize_buckle_id){
//            return $this->ajaxReturn(1, '奖扣信息有误');
        }
        $com_department_id = db('com_department')->where(['id'=>$params['department_id'], 'com_id'=>$this->com_id])->value('id');
        if(!$com_department_id){
            return $this->ajaxReturn(1, '部门信息有误');
        }
        //检查工号 手机号 账号
        $job_number_id = db('users')->where(['job_number'=>$params['job_number'], 'com_id'=>$this->com_id])->value('id');
        $name_id = db('users')->where(['name'=>$params['name'], 'com_id'=>$this->com_id])->value('id');
        $mobile_exists = db('users')->where(['mobile'=>$params['mobile'],'com_id'=>$this->com_id])->value('id');
        $u_exists_id = db('users')->where(['account'=>$params['account'],'com_id'=>$this->com_id])->value('id');
        Db::startTrans();
        try{
            $sys = M('com_config')->where('com_id',$this->com_id)->field('working_age_integral,working_age_end')->find();
            if($params['id']){

//                var_dump($u_exists_id);die;
                if($u_exists_id && ($u_exists_id != $params['id'])){
                    return $this->ajaxReturn(1, '修改失败 账号已存在');
                }
                if($job_number_id && $job_number_id != $params['id']){
                    return $this->ajaxReturn(1, '修改失败 工号已存在');
                }
                if($mobile_exists && $mobile_exists != $params['id']){
                    return $this->ajaxReturn(1, '修改失败 手机号已存在');
                }
                $user_id = db('users')->where(['id'=>$params['id'], 'com_id'=>$this->com_id])->value('id');
                if(!$user_id){
                    return $this->ajaxReturn(1, '修改失败 用户信息有误');
                }
                //计算工龄分
                if($sys['working_age_end'] > $params['entry_time']){
                    $params['working_age_integral'] = (((int)date('Y',$sys['working_age_end'])-(int)date('Y',$params['entry_time']))*12+((int)date('m',$sys['working_age_end'])-(int)date('m',$params['entry_time'])))*$sys['working_age_integral'];
                }else{
                    $params['working_age_integral'] = 0;
                }
                db('users')->where('id', $user_id)->update($params);
            }else{
                //查询企业最大人数
                $count = M('company')->where('id',$this->com_id)->value('use_people');
                //查询用户数量(不包含冻结用户)
                $where_users = [];
                $where_users['com_id'] = $this->com_id;
                $where_users['type'] = 1;
                $user_count = M('users')->where($where_users)->count();
                if($user_count >= $count){
                    return $this->ajaxReturn(1,'超过企业人员数量限制请联系管理员');
                }
                if($u_exists_id){
                    return $this->ajaxReturn(1, '添加失败 账号已存在');
                }
                if($job_number_id){
                    return $this->ajaxReturn(1, '添加失败 工号已存在');
                }
                if($mobile_exists){
                    return $this->ajaxReturn(1, '添加失败 手机号已存在');
                }
                $params['password'] = md5("123456");
                $params['c_time'] = time();
                if($sys['working_age_end'] > $params['entry_time']){
                    $params['working_age_integral'] = (((int)date('Y',$sys['working_age_end'])-(int)date('Y',$params['entry_time']))*12+((int)date('m',$sys['working_age_end'])-(int)date('m',$params['entry_time'])))*$sys['working_age_integral'];
                }else{
                    $params['working_age_integral'] = 0;
                }
                $user_id = db('users')->insertGetId($params);
            }
            //更新奖扣权限关联表
            $prize_buckle_info = db('com_prize_buckle_relation_users')->where(['user_id'=>$user_id])->find();
            $prize_buckle_data = [
                'com_id'=>$this->com_id,
                'prize_buckle_id'=>$params['cpb_id'],
                'user_id'=>$user_id
            ];
            if($prize_buckle_info){
                if(empty($params['cpb_id'])){
                    db('com_prize_buckle_relation_users')->where(['user_id'=>$user_id])->delete();
                }else{
                    db('com_prize_buckle_relation_users')->where(['user_id'=>$user_id])->update($prize_buckle_data);
                }
            }else{
                db('com_prize_buckle_relation_users')->where(['user_id'=>$user_id])->insert($prize_buckle_data);
            }
            Db::commit();
            return $this->ajaxReturn(0,"成功", url(CONTROLLER_NAME.'/staff'));
        } catch (\Exception $e) {
            Db::rollback();
            return $this->ajaxReturn(1, '添加失败'.$e->getMessage());
        }
    }

    public function staff_explode_1($where){
        set_time_limit(0);
        $list = db('users')->where($where)->order('id desc')->select();
        $conf = db('com_config')->where('com_id', $this->com_id)->find();
        if($conf){
            $now_time = time();
            $end_time = 0; //用于计算工龄分的结束时间
            //如果设置了工龄分截止时间就按该时间计算  则否按当前时间计算
            if($conf['working_age_end']){
                if($conf['working_age_end'] < $now_time){
                    $end_time = $conf['working_age_end'];
                }else{
                    $end_time = $now_time;
                }
            }else{
                $end_time = $now_time;
            }
            if($conf['working_age_type'] == 0){
                $interval = \DateInterval::createFromDateString('1 month');
            }elseif($conf['working_age_type'] == 1){
                $interval = \DateInterval::createFromDateString('1 day');
            }
            $dataTimeEnd = new \DateTime(date('Y-m-d', $end_time));
            foreach($list as $k=>$v){
                $dataTimeStart = new \DateTime(date('Y-m-d', $v['c_time']));
                $period   = new \DatePeriod($dataTimeStart, $interval, $dataTimeEnd);
                $period_num = 0;
                foreach($period as $v2){
                    $period_num++;
                }
                $list[$k]['start_integral'] = $conf['start_integral'];
                $list[$k]['working_age_integral'] = $conf['working_age_integral'] * $period_num;
            }
        }else{
            $this->error("请先设置积分配置");
        }
        $strTable = '<table width="500" border="1">';
        $strTable .= '<tr>';
        $strTable .= '<td style="text-align:center;font-size:16px;width:*">姓名</td>';
        $strTable .= '<td style="text-align:center;font-size:16px;width:*">工号</td>';
        $strTable .= '<td style="text-align:center;font-size:16px;width:120px;">账号</td>';
        $strTable .= '<td style="text-align:center;font-size:16px; width="*">性别</td>';
        $strTable .= '<td style="text-align:center;font-size:16px; width="*">生日</td>';
        $strTable .= '<td style="text-align:center;font-size:16px; width="*">手机号码</td>';
        $strTable .= '<td style="text-align:center;font-size:16px; width="*">入职日期</td>';
        $strTable .= '<td style="text-align:center;font-size:16px;width=120px;">部门</td>';
        $strTable .= '<td style="text-align:center;font-size:16px;width="*">参与排名</td>';
        $strTable .= '<td style="text-align:center;font-size:16px; width="*">基础分</td>';
        $strTable .= '<td style="text-align:center;font-size:16px; width="*">工龄分</td>';
        $strTable .= '</tr>';
        $sex = [1=>'男', 2=>'女'];
        $is_sort = [0=>'否', 1=>'是'];
        $com_department = db('com_department')->column('id, name'); //企业部门表
        if (!empty($list)) {
            foreach ($list as $key => $val) {
                $val['birthday'] && $val['birthday'] = date('Y-m-d', $val['birthday']);
                $val['entry_time'] && $val['entry_time'] = date('Y-m-d', $val['entry_time']);
                $strTable .= '<tr>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $val['name'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $val['job_number'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $val['account'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $sex[$val['sex']] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $val['birthday'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $val['mobile'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $val['entry_time'] . '</td>';
                $com_departmentName = isset($val['department_id']) && !empty($val['department_id']) ? $com_department[$val['department_id']] : '111';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $com_departmentName. '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $is_sort[$val['is_sort']] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $val['start_integral'] . '</td>';
                $strTable .= '<td style="text-align:left;font-size:16px;">' . $val['working_age_integral'] . '</td>';
                $strTable .= '</tr>';
            }
        }
        $strTable .= '</table>';
        unset($list);
        $name = '人员列表';
        downloadExcel($strTable, $name);
        exit();
    }
    public function staff_explode($where){
        set_time_limit(0);
        $list = db('users')->where($where)->order('id desc')->select();
        $conf = db('com_config')->where('com_id', $this->com_id)->find();
        if($conf){
            $now_time = time();
            $end_time = 0; //用于计算工龄分的结束时间
            //如果设置了工龄分截止时间就按该时间计算  则否按当前时间计算
            if($conf['working_age_end']){
                if($conf['working_age_end'] < $now_time){
                    $end_time = $conf['working_age_end'];
                }else{
                    $end_time = $now_time;
                }
            }else{
                $end_time = $now_time;
            }
            if($conf['working_age_type'] == 0){
                $interval = \DateInterval::createFromDateString('1 month');
            }elseif($conf['working_age_type'] == 1){
                $interval = \DateInterval::createFromDateString('1 day');
            }
            $dataTimeEnd = new \DateTime(date('Y-m-d', $end_time));
            foreach($list as $k=>$v){
                $dataTimeStart = new \DateTime(date('Y-m-d', $v['c_time']));
                $period   = new \DatePeriod($dataTimeStart, $interval, $dataTimeEnd);
                $period_num = 0;
                foreach($period as $v2){
                    $period_num++;
                }
                if(!empty($v['birthday'])){
                    $list[$k]['birthday'] = date('Y-m-d',$v['birthday']);
                }
                if(!empty($v['entry_time'])){
                    $list[$k]['entry_time'] = date('Y-m-d',$v['entry_time']);
                }
//                $list[$k]['start_integral'] = $conf['start_integral'];
//                $list[$k]['working_age_integral'] = $conf['working_age_integral'] * $period_num;

            }
        }else{
            $this->error("请先设置积分配置");
        }
        $sex = [1=>'男', 2=>'女'];
        $is_sort = [0=>'否', 1=>'是'];
        $com_department = db('com_department')->where('com_id',$this->com_id)->column('id, name'); //企业部门表
        import('PHPExcel/PHPExcel', EXTEND_PATH);
        $obj = new \PHPExcel();
        //横向单元格标识
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

        $obj->getActiveSheet(0)->setTitle(date('Y-m-d',time()).'人员列表');   //设置sheet名称
        $_row = 1;   //设置纵向单元格标识
        $title=[
            '姓名',
            '工号',
            '账号',
            '性别',
            '生日',
            '手机号码',
            '入职日期',
            '部门',
            '参与排名',
            '基础分',
            '工龄分',
        ];
        if($title){
            $i = 0;
            foreach($title AS $v){   //设置列标题
                $obj->setActiveSheetIndex(0)->setCellValue($cellName[$i].$_row, $v);

                $i++;
            }
            $_row++;
        }
        //设置样式
        $obj->getActiveSheet()->getStyle( 'A1:K1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        //设置背景样式
        $obj->getActiveSheet()->getStyle( 'A1:K1')->getFill()->getStartColor()->setARGB('FF808000');
        //设置单元格格式:文本
        $obj->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $obj->getActiveSheet()->getStyle('C')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        //设置宽度
        $obj->getActiveSheet()->getColumnDimension( 'C')->setAutoSize(true);
        $obj->getActiveSheet()->getColumnDimension( 'E')->setAutoSize(true);
        $obj->getActiveSheet()->getColumnDimension( 'F')->setAutoSize(true);
        $obj->getActiveSheet()->getColumnDimension( 'G')->setAutoSize(true);
//        $obj->getActiveSheet()->getColumnDimension( 'H')->setAutoSize(true);
         $obj->getActiveSheet()->getColumnDimension( 'H')->setWidth(20);
        //填写数据
        $data = $list;

        if($data){
            $i = 0;
            foreach($data AS $_v){
                $com_departmentName = isset($_v['department_id']) && !empty($_v['department_id']) ? $com_department[$_v['department_id']] : '';
                $obj->getActiveSheet(0)->setCellValue($cellName[0] . ($i+$_row), $_v['name']);
                $obj->getActiveSheet(0)->setCellValue($cellName[1] . ($i+$_row), $_v['job_number']);
                $obj->getActiveSheet(0)->setCellValue($cellName[2] . ($i+$_row), $_v['account']);
                $obj->getActiveSheet(0)->setCellValue($cellName[3] . ($i+$_row), $sex[$_v['sex']]);
                $obj->getActiveSheet(0)->setCellValue($cellName[4] . ($i+$_row), $_v['birthday']);
                $obj->getActiveSheet(0)->setCellValue($cellName[5] . ($i+$_row), $_v['mobile']);
                $obj->getActiveSheet(0)->setCellValue($cellName[6] . ($i+$_row), $_v['entry_time']);
                $obj->getActiveSheet(0)->setCellValue($cellName[7] . ($i+$_row), $com_departmentName);
                $obj->getActiveSheet(0)->setCellValue($cellName[8] . ($i+$_row), $is_sort[$_v['is_sort']]);
                $obj->getActiveSheet(0)->setCellValue($cellName[9] . ($i+$_row), $_v['base_integral']);
                $obj->getActiveSheet(0)->setCellValue($cellName[10] . ($i+$_row), $_v['working_age_integral']);
                $i++;
            }
        }

        //文件名处理
        $fileName = date('Y-m-d',time()).'人员列表';

        $objWrite = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
        //网页下载
        header('pragma:public');
        header("Content-Disposition:attachment;filename=$fileName.xlsx");
        $objWrite->save('php://output');
        exit;
    }

    //员工信息导入
    public function staff_import()
    {
        set_time_limit(0);
        if($_FILES['file']['error'] != 0){
            return $this->ajaxReturn(1, "文件上传有误");
        }
        //文件是否大于2M
        if($_FILES['file']['size'] > 102400*5){
            return $this->ajaxReturn(1, "文件最大5M");
        }
        $r = action('file/controller_up');
        if($r['err'] != 0){
            return $this->ajaxReturn(1, "文件保存出错");
        }
        $path = ROOT_PATH.'public'.$r['data']['saveName'];
        $type = 'Xlsx';//设置为Xls代表支持2003或以下版本，Xlsx代表2007版
        $xlsReader = IOFactory::createReader($type);
        $xlsReader->setReadDataOnly(true);
        $Sheets = $xlsReader->load($path);
        $dataArray = $Sheets->getSheet(0)->toArray();
        unlink($path);

        $data = [];
        $time = time();
        $password = md5("123456");
        //用户信息
        $userInfos = db('users')->where([ 'com_id'=>$this->com_id])->column('id,job_number,account,mobile','mobile');
        $job_numbers = array_column($userInfos,'job_number');
        $accounts = array_column($userInfos,'account');
        $mobiles = array_column($userInfos,'mobile');
        $department = M('com_department')->where([ 'com_id'=>$this->com_id])->column('id,name','name');
//        var_dump($department);die;
        $department_names = M('com_department')->where(['com_id'=>$this->com_id])->column('name');
//        var_dump($department_names);die;
        unset($dataArray[0]);       //删除第一行，从第二行开始读取
        $err_msg = '';
        $data = [];
        $config = M('com_config')->where(['com_id'=>$this->com_id])->find();
        foreach ($dataArray as $key=>$row){
            $index = $key + 1;
            if(!empty($row[0])) {
                //字段验证
                if (empty($row[0])||empty($row[1])||empty($row[2])||empty($row[3])||empty($row[4])||empty($row[5])||empty($row[6])||empty($row[8])){
                    $err_msg .= '第'.$index.'行数据缺失';
                }
                //字段属性验证
                $check = '/^1[3456789]{1}\d{9}$/';
                if (strlen($row[6]) != 11 || !preg_match($check, $row[6])){
                    $err_msg .= '第'.$index.'行手机号格式不正确';
                }
                //工号
                if (in_array($row[1], $job_numbers)){
//                    $err_msg .= '第'.$index.'行工号已存在或表单有重复';
                }else{
                    //array_push($job_numbers, $row[1]);
                }

                //账号
                if (in_array($row[2], $accounts)){
//                    $err_msg .= '第'.$index.'行账号已存在或表单有重复';
                }else{
//                    array_push($accounts, $row[2]);
                }

                //手机
                if (in_array($row[6], $mobiles)){
//                    $err_msg .= '第'.$index.'行手机号已存在或表单有重复';
                }else{
//                    array_push($mobiles, $row[6]);
                }

                //部门
                if (!in_array($row[5], $department_names)){
                    $err_msg .= '第'.$index.'行部门不存在';
                }
                //计算工龄分
                $params['entry_time'] = strtotime($row[8]);
                if($config['working_age_end'] > $params['entry_time']){
                    $params['working_age_integral'] = (((int)date('Y',$config['working_age_end'])-(int)date('Y',$params['entry_time']))*12+((int)date('m',$config['working_age_end'])-(int)date('m',$params['entry_time'])))*$config['working_age_integral'];
                }else{
                    $params['working_age_integral'] = 0;
                }
                if (in_array($row[6], $mobiles)){
                    $data_update[] = [
                        'id'=>$userInfos[$row[6]]['id'],
                        'com_id' => $this->com_id,
                        'name' => $row[0],
                        'account' => $row[2],
                        'mobile' => $row[6],
                        'department_id' => $department[$row[5]]??0,
                        'sex' => $row[3] == '男' ? 1 : 2,
                        'job_number' => $row[1],
                        'birthday' => strtotime($row[4]),
                        'email' => $row[7] ?? '',
                        'entry_time' => strtotime($row[8]),
                        'base_integral'=>$config['start_integral'],
                        'working_age_integral'=>$params['working_age_integral'],
                        'job_number' => $row[1],
                        'type'=>1
                    ];
                }else{
                    $data[] = [
                        'com_id' => $this->com_id,
                        'name' => $row[0],
                        'account' => $row[2],
                        'password' => $password,
                        'c_time' => $time,
                        'mobile' => $row[6],
                        'role_id' => '1',
                        'department_id' => $department[$row[5]]??0,
                        'sex' => $row[3] == '男' ? 1 : 2,
                        'job_number' => $row[1],
                        'birthday' => strtotime($row[4]),
                        'email' => $row[7] ?? '',
                        'entry_time' => strtotime($row[8]),
                        'base_integral'=>$config['start_integral'],
                        'working_age_integral'=>$params['working_age_integral']
                    ];
                }

            }
        }
        if(!empty($err_msg)){
            return $this->ajaxReturn(1,$err_msg);
        }
        //查询企业剩余人员数量
        $use_people = M('company')->where('id',$this->com_id)->value('use_people');
        $use_count = M('users')->where(['com_id'=>$this->com_id,'status'=>1,'type'=>1])->count();
        $count = count($data);
        if($count > ($use_people-$use_count)){
            return $this->ajaxReturn(1,'超过可新增人数限制'.($use_people-$use_count));
        }
        DB::startTrans();
        try{
            if(!empty($data)){
                M('users')->insertAll($data);
            }
            if(!empty($data_update)){
                dbSaveAll($data_update,'users','id');
            }
            DB::commit();
            return $this->ajaxReturn(0,'导入成功');
        }catch (\Exception $e){
            DB::rollback();
            return $this->ajaxReturn(1,$e->getMessage());
        }

    }

    //员工信息导入
    public function staff_import_csv()
    {
        $file = request()->file('file');
        $abs_path = ROOT_PATH.'/public';
        $upload_path  =  'uploads'.DS.date("Y").DS.date("m").DS.date("d");
        $fileNameOld = time().'_'.md5(time()+rand(0,10000));
        $info = $file->move($upload_path, $fileNameOld, false);
        $fileUrl = $info==false ? $abs_path.DS.$upload_path.DS.$fileNameOld : $abs_path.DS.$upload_path.DS.$info->getSaveName();
        $csv = Reader::createFromPath($fileUrl, 'r');
        $csv->setHeaderOffset(0);
        $data = [];
        $time = time();
        $password = md5("123456");
        //用户信息
        $userInfos = db('users')->where([ 'com_id'=>$this->com_id])->column('job_number,account,mobile');
        $job_numbers = array_column($userInfos,'job_number');
        $accounts = array_column($userInfos,'account');
        $mobiles = array_column($userInfos,'mobile');

        foreach ($csv as $key=>$v){
            $row = array_values($v);
            $row = implode(',',$row);
            $row= mb_convert_encoding($row, "UTF-8", "GBK"); //已知源码为GBK，转换为utf-8
            $row = explode(',', $row);

            //字段验证
            if(!$row[0]) return $this->ajaxReturn(1,'姓名不能为空');
            if(!$row[1]) return $this->ajaxReturn(1,'工号不能为空');
            if(!$row[2]) return $this->ajaxReturn(1,'账号不能为空');
            if(!$row[3] || !in_array($row[3],['男','女'])) return $this->ajaxReturn(1,'性别不能为空或填写错误');
            if(!strtotime($row[4])) return $this->ajaxReturn(1,'生日不能为空或格式不正确');
            if(!$row[5]) return $this->ajaxReturn(1,'部门不能为空');
            if(!$row[6]) return $this->ajaxReturn(1,'手机号不能为空');
            if(!strtotime($row[8])) return $this->ajaxReturn(1,'入职日期不能为空或格式不正确');

            //字段属性验证
            $check = '/^1[3456789]{1}\d{9}$/';
            if(strlen($row[6]) != 11 || !preg_match($check, $row[6])) return $this->ajaxReturn(1,'手机号格式不正确');
            if($row[7] && !filter_var($row[7], FILTER_VALIDATE_EMAIL)) return $this->ajaxReturn(1,'邮箱格式不正确');

            //工号
            if(in_array($row[1], $job_numbers)) return $this->ajaxReturn(1,'工号'.$row[1].'已存在或表单中有重复');
            array_push($job_numbers,$row[1]);
            //账号
            if(in_array($row[2], $accounts)) return $this->ajaxReturn(1,'账号'.$row[2].'已存在或表单中有重复');
            array_push($accounts,$row[2]);
            //手机
            if(in_array($row[6], $mobiles)) return $this->ajaxReturn(1,'手机号'.$row[6].'已存在或表单中有重复');
            array_push($mobiles,$row[6]);
            //部门
            if(!$departmentId = db('com_department')->where([ 'com_id'=>$this->com_id, 'name'=>$row[5]])->find()) return $this->ajaxReturn(1,'部门'.$row[5].'不存在');

            $data[] = [
                'com_id' => $this->com_id,
                'name'=>$row[0],
                'account'=>$row[2],
                'password'=>$password,
                'c_time'=>$time,
                'mobile'=>$row[6],
                'role_id'=>'1',
                'department_id'=>$departmentId,
                'sex'=>$row[3] == '男' ? 1 : 2,
                'job_number'=>$row[1],
                'birthday'=>strtotime($row[4]),
                'email'=>$row[7]??'',
                'entry_time'=>strtotime($row[8])
            ];
        }

        DB::startTrans();
        try{
            echo M('users')->insertAll($data);
            DB::commit();
            return $this->ajaxReturn(0,'导入成功');
        }catch (\Exception $e){
            DB::rollback();
            return $this->ajaxReturn(1,$e->getMessage());
        }

    }

    //
    public function staff_explode_bak($where){
        $csv = Writer::createFromFileObject(new \SplTempFileObject());
        $csv->setDelimiter(',');
        $csv->setNewline("\r\n");
        $csv->insertOne(['姓名', '工号', '账号','性别','生日','手机号码','入职日期','部门','参与排名','基础分','工龄分']);

        $list = db('users')->where($where)->order('id desc')->select();
        $conf = db('com_config')->where('com_id', $this->com_id)->find();
        if($conf){
            $now_time = time();
            $end_time = 0; //用于计算工龄分的结束时间
            //如果设置了工龄分截止时间就按该时间计算  则否按当前时间计算
            if($conf['working_age_end']){
                if($conf['working_age_end'] < $now_time){
                    $end_time = $conf['working_age_end'];
                }else{
                    $end_time = $now_time;
                }
            }else{
                $end_time = $now_time;
            }
            if($conf['working_age_type'] == 0){
                $interval = \DateInterval::createFromDateString('1 month');
            }elseif($conf['working_age_type'] == 1){
                $interval = \DateInterval::createFromDateString('1 day');
            }
            $dataTimeEnd = new \DateTime(date('Y-m-d', $end_time));
            foreach($list as $k=>$v){
                $dataTimeStart = new \DateTime(date('Y-m-d', $v['c_time']));
                $period   = new \DatePeriod($dataTimeStart, $interval, $dataTimeEnd);
                $period_num = 0;
                foreach($period as $v2){
                    $period_num++;
                }
                $list[$k]['start_integral'] = $conf['start_integral'];
                $list[$k]['working_age_integral'] = $conf['working_age_integral'] * $period_num;
            }
        }else{
            $this->error("请先设置积分配置");
        }
        $sex = [1=>'男', 2=>'女'];
        $is_sort = [0=>'否', 1=>'是'];
        $com_department = db('com_department')->column('id, name'); //企业部门表
        $tmpArr = [];
        if (!empty($list)) {
            foreach ($list as $key => $val) {
                $val['birthday'] && $val['birthday'] = date('Y-m-d', $val['birthday']);
                $val['entry_time'] && $val['entry_time'] = date('Y-m-d', $val['entry_time']);
                $tmpArr[$key][] = $val['name'];
                $tmpArr[$key][] = $val['job_number'];
                $tmpArr[$key][] = $val['account'];
                $tmpArr[$key][] = $sex[$val['sex']];
                $tmpArr[$key][] = $val['birthday'];
                $tmpArr[$key][] = $val['mobile'];
                $tmpArr[$key][] = $val['entry_time'];
                $com_departmentName = isset($val['department_id']) && !empty($val['department_id']) ? $com_department[$val['department_id']] : '111';
                $tmpArr[$key][] = $com_departmentName;
                $tmpArr[$key][] = $is_sort[$val['is_sort']];
                $tmpArr[$key][] = $val['start_integral'];
                $tmpArr[$key][] = $val['working_age_integral'];
            }
        }
        $csv->insertAll($tmpArr);
        $csv->output('users.csv');
        exit();
    }
    //清除用户积分
    public function staff_clear_integral(){
        $id = I('id',0);
        if(empty($id)){
            return return_json(1,'用户id不能为空');
        }
        DB::startTrans();
        try{
            $integral_b = SendIntegralNew::statistic_integral(strtotime('2010-01-01'),time(),$id);
            $integral_a = SendIntegralNew::statistic_integral_a(strtotime('2010-01-01'),time(),$id);
            $v1 = [
                'com_id'=>$this->com_id,
                'user_id'=>$id,
                'name'=>'积分清零',
            ];
            if(!($integral_b == 0 && $integral_a == 0)) {
                if($integral_b > 0){
                    $integral_b =  - $integral_b;
                }else{
                    $integral_b = abs($integral_b);
                }
                if($integral_a > 0){
                    $integral_a =  - $integral_a;
                }else{
                    $integral_a = abs($integral_a);
                }
                SendIntegralNew::add_record_integral($v1, $integral_b, $integral_a, 30, true);
            }
            //清除用户积分
            M('users')->where('id',$id)->update(['integral_a'=>0,'integral_b'=>0,'working_age_integral'=>0]);
            //清除用户记录
            M('com_integral_log')->where('user_id',$id)->delete();
            //获取用户积分奖扣分值
            DB::commit();
            return return_json(0,'清除成功');
        }catch (\Exception $e){
            DB::rollback();
            return return_json(1,$e->getMessage());
        }
    }

}